Intermediate PHP and MySQL

Introduction

Hello, and welcome back. I hope you enjoyed digging deeper into PHP in the last lesson. Today we'll turn our focus away from PHP for a bit and look at the database requirements for our course project.

The success of any Web application depends on the database behind it. Often an application lives or dies by how quickly it can produce information on Web pages. Customers don't like to wait in long lines in stores, and Web customers are even more impatient waiting for Web pages to load! So in this lesson, we'll set up a solid database foundation to handle our Food Store application.

First, we'll look at the database tables we'll need to hold all of our data. You can imagine that to track products, customers, and orders, we'll need a few different tables at our disposal.

Following that, we'll see some tricks we can use in MySQL to help organize our data. MySQL is a relational database, and it provides some features to help keep related data from getting out of sync.

Finally, we'll start looking at some advanced SQL queries and see what we can do in MySQL to help manage the data retrieval process.

That should keep us busy for the day! Let's move on to Chapter 2 and start laying out our database.

The Database Structure

Before you can start coding the Food Store application, we'll need to talk about the database structure. Because the database is an important part of the application, it's in your best interest to spend a few minutes (or even hours) thinking through the database requirements before you start coding.

The Products Table

For the Food Store application, the first thing we'll need to be able to do is track the products in our store. So the first database object to look at is the products table. You'll need to create data fields in the products table to hold all of the information related to an individual product. Here's what you'll need to track:

You may notice a couple data types in the products table that you haven't seen before. The price field uses the decimal data type. The decimal data type allows us to specify exactly how many decimal places should be in the data value.

Some programmers use a floating point data type for money values. This can get ugly, as there's no way to control how many decimal places are stored. If you have a value of six dollars, the database will only store it as a value of 6.

The decimal(6,2) data type defines that there will always be six digits in our value, and it ensures that there will always be two decimal places. This means we can store values up to $9,999.99 in this field.

The onsale field uses the Boolean data type (abbreviated BOOL in MySQL). This is a simple, one-bit value that gets set to either zero or one. The manager of the Food Store Web site can set an item to either be on sale (set to a value of 1) or not on sale (set to a value of zero). Later on, you'll see how to use this field in association with a simple check box in our administration area. All the manager will need to do is select a check box to indicate that an item is on sale.

Finally, I'm sure you're interested in the picture data field. Yes, we're going to store images in our database to display on the Web! This is a hot topic in the world of dynamic Web pages.

MySQL can store any type of binary data using the Binary Large Object (BLOB) data type. It lets you store anything from video clips to Excel spreadsheets. This feature can make your database extremely versatile. There's no limit as to what objects you can store for later retrieval.

There are four sizes of BLOB data types:

• Tinyblob - for data up to 255 bytes
• Blob - for data up to 65,535 bytes
• Mediumblob - for data up to 16,777,215 bytes
• Longblob - for data up to 4GB

The mediumblob data type that we'll use to store our pictures can handle data up to about 16 MB of size! We won't be using that much space for our images, but it's nice to know it's there if we need it.

The Categories Table

The categories table holds information on the product categories. We'll divide our Food Store into separate categories based on types of food. You probably wouldn't want to force your customers to scan through a single Web page looking for individual products. Instead, we'll divide our Web site into product categories to help limit the number of items (and pages) they have to wade through.

The categories table tracks the category names and includes two data fields:

• A catid integer data field, which defines a unique category ID for each category
• A name data field using a varchar(30) data type to define the name of the category

We'll allow our store managers to create and remove categories as they build the storefront. This table will help us keep track of what categories are available.

The Customers Table

The next thing you'll need to track is your customer information. You'll create a customers table that will include the following fields:

There's nothing too exciting about the customers table. It stores all of the customer information in text format (with the obvious exception of the customer ID value that MySQL will assign automatically using the AUTO_INCREMENT feature).

Tracking Customer Orders

We'll also need a way to track customer orders. We don't want to include orders as a field within the customer table, because a single customer could (hopefully) have more than one order during his or her lifetime. So, we'll create a separate table to handle customer orders, then link it back to the customers table so we know which customer placed which orders.

Another challenge we'll have is designing an orders table that lets us track what items a customer purchases in a given order. This can get tricky, since we're trying to combine data from two sources (tracking a customer order and then tracking the products in that customer order). To solve this problem, we'll break our customer orders into two separate tables.

The orders table contains the bare-bones information we need to track a customer order.

That will help us keep track of individual orders. Now we need to relate what products a customer purchases in a given order. You'll do that by creating the order_items table.

The order_items table records have a unique feature. Notice that no one data field works as a primary key. There can be multiple products purchased in a single order, and an individual product can be purchased in many different orders. What makes a record unique in the order_items table is a combination of the orderid and prodid data fields.

This is an example of a multi-field primary key. MySQL allows us to select a combination of two or more fields to use as the primary key for the table. In this case, we'll use two data fields to uniquely define the primary key for each record.

That covers all of the data necessary for our customers and products. However, there's still one more piece of information we'll want to track. The Food Store application allows one or more managers to log in and manage the storefront. Once a manager logs in, he or she can add and modify products and process orders. You'll need to create a way to track managers who are allowed to do these functions. To do that, you simply include a table to track a manager account and password ‘ the admins table.

That defines the tables we'll be using in the Food Store application. In the next chapter, you'll start building the tables, and we'll look at a way to manage the data in our tables by taking advantage of the relationships we created.

Building Relational Tables

Since MySQL is a relational database, it has the capability of relating two or more tables together using links called constraints.

A constraint is a rule placed on a data field that restricts the data you're allowed to place in it. You can create a constraint that only allows you to enter positive values in a numeric data field or only lowercase letters in a character data field. A relational database utilizes constraints to allow only data contained in one table to be entered into a data field in another table, thus creating the relationship.

For example, the catid data field in the products table is directly related to the catid data field in the categories table. You shouldn't be able to enter a product record with a catid value that doesn't appear in the categories table.

The relational database server handles all of the data relationships for you automatically. This allows you to know your data will always be in sync. The problem is, you need to tell MySQL about these relationships.

If you create just individual tables in MySQL, no relationships exist between the tables. Then MySQL will let you enter any data you want into the products table even if there isn't a corresponding catid value.

We need to tell MySQL that the catid data field in the products table is related to the catid data field in the categories table. You do this by using a foreign key constraint.

When you define a data field as a foreign key, MySQL establishes an internal constraint between that data field and the data field referenced in another (foreign) table. The data field in the other table must be the primary key for that table; so only one record can be related to the foreign key value.

Let's take a look at our database design layout and see how the table data fields are related.

The Food Store database layout

I've designed the layout for each of the tables to easily accommodate foreign keys. Notice that the products table relates to the categories table by incorporating the catid primary key value from the categories table as a data field. The order_items table uses the prodid primary key from the products table to uniquely identify products purchased.

Similarly, the orders table relates to the customers table using the custid primary key data field to uniquely identify the customer who placed the order. The order_items table relates to both the orders and products tables by using both the orderid and prodid primary key data fields.

Now that we've established the design of our database, you'll need to create the tables and relate the foreign key data fields. Unfortunately, this can be problematic.

Creating Tables With Foreign Keys

Sadly, our trusty friend the phpMyAdmin graphical database tool can help us out only so far. It lacks the ability to implement the more exotic database features. Unfortunately, phpMyAdmin doesn't allow you to define foreign keys when you create a table using the graphical interface. For that, you'll need to perform some manual interventions.

To create the tables for our project, you'll need to use the MySQL Console feature in WampServer. The MySQL Console gives you a command prompt interface, which allows you to enter SQL commands directly to the MySQL server. To get to the MySQL Console, follow these steps:

1. Start the WampServer services by clicking Start > Programs > WampServer > start WampServer.
2. Click the WampServer icon in the system tray.
3. Select MySQL > MySQL Console.

The MySQL Console window appears, asking you for the root password. The default root password in WampServer is empty; so just press the ENTER key. Next you're greeted by a short message and then the MySQL prompt.

The MySQL Console window

At the prompt, you must first connect to a database using the connect statement. Once connected to a database, you can enter any SQL statement for the database. The MySQL server processes the SQL and returns the results in the Console window.

For this application, you'll need to create a new database, connect to it, and then create the tables. Follow these steps:

1. Enter the following SQL statement to create a database called store:

   CREATE DATABASE store DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci;

   Note: The semicolon at the end of the SQL statement is important. It tells MySQL that your statement is ready for processing. You can use that to your advantage by spreading out a long SQL statement over several lines. Just don't use the semicolon at the end of a line. If you do, MySQL will provide a secondary prompt for you to enter more text for the SQL statement.

   This creates the store database and sets the default collation using the Unicode case-insensitive sorting method (utf8_unicode_ci), which uses the multi-lingual UTF8 character set with case-insensitive sorting. You should get a message back indicating that the SQL statement completed successfully.

2. Enter the connect command to connect to the new database:

   connect store;

3. Enter the SQL CREATE TABLE statement to create the categories table:

   CREATE TABLE  categories(catid int NOT NULL AUTO_INCREMENT PRIMARY KEY,
             name varchar(30) NOT NULL) ENGINE=INNODB;

This creates the categories table using the InnoDB database engine.

4. Enter the SQL CREATE TABLE statement to create the products table:

   CREATE TABLE products (prodid int NOT NULL AUTO_INCREMENT PRIMARY KEY,
             catid int NOT NULL,
             description varchar(100) NOT NULL,
             picture mediumblob,
             price decimal(6,2) NOT NULL,
             quantity int NOT NULL,
             onsale BOOL,
             FOREIGN KEY (catid) REFERENCES categories(catid))
             ENGINE=INNODB; 

   This creates the products table using the InnoDB database engine. We specified the foreign key constraint in the CREATE TABLE SQL statement by using the FOREIGN KEY clause. This matches the catid data field in this table to the catid data field in the categories table.

5. To exit the MySQL Console, type exit at the command prompt.

Congratulations, you have now manually created a database and two tables from the MySQL Console! Now, let's walk through some SQL testing to see if the foreign key constraint really works.

1. Start the MySQL Console by clicking the WampServer icon in the system tray, then selecting MySQL > MySQL Console.
2. Connect to the store database:

   connect store;

3. Use the INSERT SQL statement to insert a new record into the products table without creating a category in the categories table:

   INSERT INTO products (catid, description, price, quantity) VALUES (1, "test", 6.25, 10);

4. Examine the message that MySQL returns:

   ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint
fails (`store2/products`, CONSTRAINT `products_ibfk_1` FOREIGN KEY (`catid`) REFERENCES `categories` (`catid`))

The MySQL database automatically rejected our attempt to insert a new products record without a corresponding catid in the categories table!

You'll be creating the rest of the tables in this lesson's assignment. For now, let's move on to Chapter 4 and look at some fancy queries.

Using Complex Database Queries

The key to any dynamic Web application is the database and retrieving information from it. In a simple database layout, data is usually stored in a single table. There's no need to query multiple tables looking for data.

In a simple database environment, you see SQL query statements that look like this:

SELECT recipeid, title, poster, shortdesc FROM recipes ORDER BY recipeid DESC

This simple SQL statement just retrieves four data fields from a single table. In more complex applications, data is often split between multiple tables, as you see in our products and categories tables. If we list products from the products table, all we see is a catid value, not the real category name. To extract meaningful information, you need to look up the catid in the categories table to find the full category name. This makes displaying the product information more meaningful, as it just uses words instead of database codes.

Fortunately, SQL allows us to extract related information from two tables within a single SQL query. Because we're using two tables at the same time, the query itself gets a lot more complicated than what you may be used to seeing. But the results are well worth the extra effort.

The list of data fields is more complicated because you must identify which table each data field is from. You can do that by using this format: table.datafield

When you use the FROM clause, you need to list all of the tables used in the query. And the WHERE clause must provide a statement that specifies which data fields should be related in the query.

Let's take a look at an example of this. Here's a query that lists the names and addresses of the customers associated with each order:

SELECT products.description, categories.name, products.quantity FROM products, categories WHERE products.catid = categories.catid;

This example extracts the category name from the categories table and the product description and quantity in stock from the products table to produce a report that actually makes sense. The relation between these two tables is the catid data field.

You need the WHERE clause to let MySQL know to combine the two tables by matching the catid data values between them. Let's test drive this SQL statement and see what it gets us. First, you'll need to insert some test data into the tables:

1. Start the MySQL Console from the WampServer icon.
2. Connect to the store database.

   connect store;

3. Insert a few records into the categories table:

   INSERT into categories (name) VALUES ("produce");
   INSERT into categories (name) VALUES ("fruit");
   INSERT INTO categories (name) VALUES ("bread");

4. Now, insert a few products into the products table:

   Print code
   

   INSERT INTO products (catid, description, price, quantity) VALUES (1, "eggplant", 1.25, 20);
   INSERT INTO products (catid, description, price, quantity) VALUES (1, "tomatoes", 2.00, 15);
   INSERT INTO products (catid, description, price, quantity) VALUES (2, "bananas", 1.25, 5); 

5. Use the SELECT SQL statements to ensure your data is in the tables.

Now that you have some data, let's test our fancy SQL query. From the MySQL prompt, enter the SQL statement:

MySQL processes the query and displays the result set as shown below:

The result of the complex query

These results are much better than just throwing a category code at our customers!

Notice that all the WHERE clause does is match the two related data fields in the two tables. That's a lot of typing to do just to match two data fields that already have the same name. You'd think that MySQL could do that for us. Guess what? SQL uses a special SELECT syntax that does this automatically!

Joining Tables

You use the JOIN keyword to join two tables together on a common data field name. Here's an example of the same query using the JOIN keyword:

The JOIN keyword is in the middle of the two listed tables in the FROM clause. It also requires the USING clause to specify the data field that relates the two tables. You must specify the data field within the parentheses.

Creating Database Views

Now you've seen a complex query that can extract data from two tables based on a common data field name. This process also works for multiple tables that share data field names. However, it can get pretty complicated when you're working with multiple tables.

The MySQL InnoDB database engine provides yet another advanced feature to help us out with commonly used complex queries. A database view is a snapshot of the database query results presented as a single logical table. The database handles the view as a normal table. You can use the view in SQL queries just as you would a regular table. The data fields in the view consist of data fields from the query result set.

It's important to remember that the view isn't a real table. It's a temporary table that MySQL creates on the fly from data contained in other tables as you query the view.

What's also great about views is they can also include SQL functions and calculations on data, providing a wealth of possibilities. Let's take a look at a sample view:

CREATE VIEW prod_view AS SELECT products.description, categories.name, products.price, products.quantity, products.price * products.quantity as value FROM products JOIN categories USING (catid);

This view creates a new view called prod_view from a standard SQL SELECT statement. It includes five data fields: the product description, the name of the product's category, the product's price, the product's quantity in stock, and a calculated data field called value. The value field is a result of multiplying the product price and the quantity in stock. The AS keyword allows you to assign a specific name to a data field in the result set. This comes in handy when you have calculated fields in a view.

Okay, let's create this view and see what happens:

1. Start the MySQL Console from the WampServer icon.
2. Connect to the store database.
3. Create the view using the SQL code shown above.

Now let's test drive the new view. From the MySQL Console prompt, enter a simple SQL query to extract the data from the view.

SELECT * from prod_view;

If all goes well, you should get a listing of the products along with the newly calculated values.

Creating and testing the prod_view view

You can now create advanced SQL queries, store them as views, and recall them at any time in your PHP code as simple SQL queries. That's pretty powerful stuff! Okay, enough for today. Let's move on to the Summary and wrap things up.

Summary

As usual, we covered a lot of ground today. First, we took a quick tour of the database requirements for the Food Store project. We discussed the new data types that we'll be using: BLOB, decimal, and Boolean. We'll see later on how to handle data for each of these data types in our PHP programs.

Next, we looked at how to use the relational feature of the MySQL server. MySQL's foreign key feature can help you keep your database data organized. Foreign keys relate a data field in one table to a primary key in another table. This ensures that customers can't enter data into a table if the data doesn't exist in the table where the data originated.

Finally, we created complex SQL queries. Combining data from multiple tables based on foreign keys is a great tool for producing complex reports. Often you need data pieces from one table while trying to display data contained in another table. The JOIN SQL keyword helps keep things manageable by automatically relating similar data fields in multiple tables.

To make our lives even easier, the MySQL InnoDB database engine allows us to create database views. A view is a temporary table that MySQL creates based on the results of a query. Instead of having to write out the complex query all the time, you can just access the simpler view data fields to extract your information.

In the next lesson, we'll dive back into PHP coding and look at the administration side of the Food Store project. We'll do some things that should be familiar to you, and we'll also incorporate some new features.

In the meantime, be sure to do this lesson's assignment‘it's crucial for getting your project code to work.